﻿using CIS.Web.Common;
using CIS.Web.Models;
using CIS.Web.Properties;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Data.Entity;
using System.IO;
using System.Linq;
using System.Linq.Dynamic;
using System.Threading.Tasks;
using System.Web.Mvc;
using static System.Net.Mime.MediaTypeNames;

namespace CIS.Web.Controllers
{
	[RoutePrefix("ConferenceEventInformation")]
	public class ConferenceEventInformationController : BaseDbController
	{
		[Route("Index/{ConferenceId}")]
		public async Task<ActionResult> Index(string conferenceId)
		{
			string controller = RouteData.Values["controller"].ToString();
			string actionName = RouteData.Values["action"].ToString();
			Utility.InsertLog(LoginUser, Request.Url.ToString(), controller, actionName, "Index", conferenceId);

			var conferenceMaster = await DbContext.ConferenceMaster.AsNoTracking()
					.FirstOrDefaultAsync(x => x.Status == ConstValues.Status.正常 &&
						x.ID == conferenceId);

			ViewBag.ConferenceId = conferenceId;
			ViewBag.ConferenceName = conferenceMaster.ConferenceName;
			ViewBag.ControllerName = Resources.ConcurrentEventInformationMaster;
			ViewBag.Language = conferenceMaster.IsInternational ? ConstValues.Language.En : ConstValues.Language.Cn;

			var model = new ConferenceEventInformationMasterModel();
			model.ConferenceId = conferenceId;
			LoginUser.ConferenceFunctionIdList = Utility.GetConferenceFunctionIdList(conferenceId);
			return View(model);
		}

		public async Task<JsonCamelCaseResult> GetData(int? page, int? rows, string sort, string order, ConferenceEventInformationMasterModel model)
		{
			int take = rows ?? ConstValues.DefaultGridRows;
			int skip = ((page ?? 1) - 1) * take;

			var sql = from attendenceInfo in DbContext.ConferenceAttendenceInfo.AsNoTracking().Where(x => x.ConferenceId == model.ConferenceId)
				  join profilePaymentInfo in DbContext.ProfilePaymentInfo.AsNoTracking().Where(x => x.FeeType == ConstValues.FeeType.同期活动费)
				       on attendenceInfo.ID equals profilePaymentInfo.AttendenceId
				  join feeConcurrentEventMaster in DbContext.FeeConcurrentEventMaster.AsNoTracking()
					on profilePaymentInfo.FeeId equals feeConcurrentEventMaster.ID
				  join master in DbContext.ConferenceMaster.AsNoTracking()
					on attendenceInfo.ConferenceId equals master.ID
				  join identity in DbContext.AttendenceIdentityMaster.AsNoTracking()
					on attendenceInfo.AttendenceIdentityId equals identity.ID
				  join concurrentEventMaster in DbContext.ConcurrentEventMaster.AsNoTracking()
					on feeConcurrentEventMaster.ConcurrentEventId equals concurrentEventMaster.ID
				  orderby attendenceInfo.Sort ascending, attendenceInfo.UpdateDate descending
				  select new
				  {
					  Id = attendenceInfo.ID,
					  FullName = attendenceInfo.FullName,
					  Affiliation = attendenceInfo.Affiliation,
					  Telephone = attendenceInfo.Telephone,
					  Mobile = attendenceInfo.Mobile,
					  Mail = attendenceInfo.Mail,
					  IdentityName = identity.IdentityName,
					  Number = profilePaymentInfo.Count,
					  Fee = profilePaymentInfo.Fee,
					  Currency = profilePaymentInfo.Currency,
					  Remark = attendenceInfo.Remark,
					  HasPaid = attendenceInfo.HasPaid,
					  EventName = concurrentEventMaster.EventName,
					  CreateDate = profilePaymentInfo.CreateDate,
					  PaymentInfoId = profilePaymentInfo.ID
				  };
			if (!string.IsNullOrWhiteSpace(model.searchEventName) && model.searchEventName != "")
			{
				sql = sql.Where(x => x.EventName.Contains(model.searchEventName));
			}


			int total = await sql.CountAsync();
			if (!string.IsNullOrEmpty(sort))
			{
				sql = sql.OrderBy(sort + ConstValues.chrSpace + order).Skip(skip).Take(take);
			}
			else
			{
				sql = sql.OrderByDescending(x => x.CreateDate).Skip(skip).Take(take);
			}

			var sqlResult = await sql.ToListAsync();
			var result = sqlResult.Select(x => new
			{
				Id = x.Id,
				FullName = x.FullName,
				Affiliation = x.Affiliation,
				Mobile = string.IsNullOrEmpty(x.Mobile)? x.Telephone : x.Mobile,
				Mail = x.Mail,
				IdentityName = x.IdentityName,
				Number = x.Number,
				TotalFee = (x.Fee * x.Number).Value.ToString("N2"),
				Currency = x.Currency,
				Remark = x.Remark,
				HasPaid = x.HasPaid,
				EventName = x.EventName,
				CreateDate = x.CreateDate,
				PaymentInfoId = x.PaymentInfoId
			});

			return JsonCamelCase(new
			{
				Total = total,
				Rows = result,
			},
			JsonRequestBehavior.AllowGet);
		}

		[HttpPost]
		[ValidateAntiForgeryToken]
		public async Task<FileResult> Export(string allId,string conferenceIdForExport)
		{
			string controller = RouteData.Values["controller"].ToString();
			string actionName = RouteData.Values["action"].ToString();
			Utility.InsertLog(LoginUser, Request.Url.ToString(), controller, actionName, "Index", conferenceIdForExport);

			var ids = allId.Split(ConstValues.chrComma);
			var sql = from attendenceIdentityMaster in DbContext.ConferenceAttendenceInfo.AsNoTracking()
				  join profilePaymentInfo in DbContext.ProfilePaymentInfo.AsNoTracking().Where(x => ids.Contains(x.ID.ToString()))
				       on attendenceIdentityMaster.ID equals profilePaymentInfo.AttendenceId
				  join feeConcurrentEventMaster in DbContext.FeeConcurrentEventMaster.AsNoTracking()
					on profilePaymentInfo.FeeId equals feeConcurrentEventMaster.ID
				  join master in DbContext.ConferenceMaster.AsNoTracking()
					on attendenceIdentityMaster.ConferenceId equals master.ID
				  join identity in DbContext.AttendenceIdentityMaster.AsNoTracking()
					on attendenceIdentityMaster.AttendenceIdentityId equals identity.ID
				  join concurrentEventMaster in DbContext.ConcurrentEventMaster.AsNoTracking()
					on feeConcurrentEventMaster.ConcurrentEventId equals concurrentEventMaster.ID
				  orderby attendenceIdentityMaster.Sort ascending, attendenceIdentityMaster.UpdateDate descending
				  select new
				  {
					  Id = attendenceIdentityMaster.ID,
					  FullName = attendenceIdentityMaster.FullName,
					  Affiliation = attendenceIdentityMaster.Affiliation,
					  Telephone = attendenceIdentityMaster.Telephone,
					  Mobile = attendenceIdentityMaster.Mobile,
					  Mail = attendenceIdentityMaster.Mail,
					  IdentityName = identity.IdentityName,
					  Number = profilePaymentInfo.Count,
					  Fee = profilePaymentInfo.Fee,
					  Currency = profilePaymentInfo.Currency,
					  Remark = attendenceIdentityMaster.Remark,
					  HasPaid = attendenceIdentityMaster.HasPaid,
					  EventName = concurrentEventMaster.EventName,
					  CreateDate = profilePaymentInfo.CreateDate
				  };

			var result = await sql.ToListAsync();

			var export = Request.Cookies["_culture"].Value == ConstValues.Language.Cn ?
					Resources.export_concurrentEventInfoCN : Resources.export_concurrentEventInfoEN;

			XSSFWorkbook workbook;

			using (MemoryStream readStream = new MemoryStream(export))
			{
				workbook = new XSSFWorkbook(readStream);
				ISheet sheet = workbook.GetSheetAt(0);

				var count = 0;
				foreach (var model in result)
				{
					count += 1;
					IRow row = sheet.CreateRow(count);

					// 活动名称
					int column = 0;
					ICell cell = row.CreateCell(column);
					cell.SetCellValue(model.EventName);

					// 姓名
					column++;
					cell = row.CreateCell(column);
					cell.SetCellValue(model.FullName);

					// 姓名
					column++;
					cell = row.CreateCell(column);
					cell.SetCellValue(model.Affiliation);

					// 联系电话
					column++;
					cell = row.CreateCell(column);
					cell.SetCellValue(string.IsNullOrEmpty(model.Mobile)? model.Telephone : model.Mobile);

					// 邮箱
					column++;
					cell = row.CreateCell(column);
					cell.SetCellValue(model.Mail);

					// 参会身份
					column++;
					cell = row.CreateCell(column);
					cell.SetCellValue(model.IdentityName);

					// 人数
					column++;
					cell = row.CreateCell(column);
					cell.SetCellValue(model.Number.ToString());

					// 总价
					column++;
					cell = row.CreateCell(column);
					var totalFee = (model.Fee * model.Number);
					cell.SetCellValue(totalFee.Value.ToString("N2"));

					// 币种
					column++;
					cell = row.CreateCell(column);
					cell.SetCellValue(model.Currency);

					// 支付状态
					column++;
					cell = row.CreateCell(column);
					cell.SetCellValue(model.HasPaid ? Resources.HasPaid:Resources.HasNotPaid);

					// 备注
					column++;
					cell = row.CreateCell(column);
					cell.SetCellValue(model.Remark);


					
				}
			}

			MemoryStream exportStream = new MemoryStream();
			workbook.Write(exportStream);
			exportStream.Flush();
			exportStream.Position = 0;

			return File(exportStream, Application.Octet,
					$"export_user_{DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx");
		}
	}
}